package dbms;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class Postgres {

	private static Postgres instance = null;

	private Connection conn = null;
	private Statement stat = null;
	private ResultSet rs = null;

	private Postgres() {
		getConnection();
	}

	private void getConnection() {

		try {
			Class.forName("org.postgresql.Driver");
			String url = "jdbc:postgresql://localhost:5432/anagraficaweb";
			Properties props = new Properties();
			props.setProperty("user", "postgres");
			props.setProperty("password", "postgres");
			conn = DriverManager.getConnection(url, "postgres", "postgres");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public ResultSet getLine() {
		try {
			stat = conn.createStatement();
			rs = stat.executeQuery("SELECT * FROM anagrafica");
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return rs;
	}

	public ResultSet getAnagraficaById(int id) {
		try {
			stat = conn.createStatement();
			rs = stat.executeQuery("SELECT * FROM anagrafica WHERE id='" + id
					+ "'");
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return rs;
	}

	public ResultSet getAllJob(int id) {
		try {
			stat = conn.createStatement();
			rs = stat
					.executeQuery("SELECT *,occupazioni.descrizione FROM lavora,occupazioni WHERE id_persona="
							+ id
							+ " AND lavora.id_occupazione=occupazioni.occupazione");
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return rs;
	}

	public ResultSet loginQuery(String pw, String user) {
		try {
			stat = conn.createStatement();
			rs = stat.executeQuery("SELECT * FROM utenti WHERE username='"
					+ user + "' AND password='" + pw + "'");
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return rs;
	}
	
	public boolean deleteAnagraficaById(int id) {
		boolean deleted = false;
		
		try {
			stat = conn.createStatement();
			rs = stat.executeQuery("SELECT * FROM anagrafica WHERE id="
					+ id);
			deleted = true;
		} catch (SQLException e) { e.printStackTrace(); }

		return deleted;
	}

	public void closeStatement() {
		try {
			stat.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static synchronized Postgres getInstance() {
		if (instance == null) {
			instance = new Postgres();
		}

		return instance;
	}

}
